<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
require_once "../dbmngt/connect.php";
require_once "../ldap/ldap.php";
//Ajout JTA 
require_once '../distance/calculDistance.php';
require_once '../html/escaping.php';
// Fin Ajout

function faireUpdateOPCA($nom, $url, $representant, $mail, $tel, $anneesFinancees, $commentaires) {

    return "NOK : methode pas encore implémentée";
    
    //error_log("faire inscrire");
    $conn = doConnection("fil_entr");
    if (strlen($nom) == 0)
        return "Saisissez un nom pour OPCA!";

    $queryString = "select nom from opca where upper(nom) like '" .
            (str_replace("-", "%", str_replace("_", "%", str_replace(" ", "%", strtoupper($nom)))))
            . "';";
    //echo $queryString;
    $opcas = mysql_query($queryString, $conn);
    $ligne = mysql_fetch_row($opcas);
    if ($ligne) {
        return "Une OPCA portant ce nom (" . $ligne[0] . ") se trouve déjà dans la base!";
    }
    $cle = str_replace("-", "_", str_replace(" ", "_", strtolower($nom)));

    $queryString = "insert into opca(`nom`, `commentaires`, `anneesFinancees`, `representant`, `tel`, `mail`, `opcaCle`, `url`)
            values" .
            "('" . doDBEscape($nom) . "'," .
            "'" . doDBEscape($commentaires) . "'," .
            "'" . doDBEscape($anneesFinancees) . "'," .
            "'" . doDBEscape($representant) . "'," .
            "'" . doDBEscape($tel) . "'," .
            "'" . doDBEscape($mail) . "'," .
            "'" . doDBEscape($cle) . "'," .
            "'" . doDBEscape($url) . "'" .
            ")";
    //echo $queryString;
    $opcas = mysql_query($queryString, $conn);

    if ($opcas == FALSE) {
        return "Problème avec la requête: $queryString<br/>Raison : " . mysql_error($conn);
    }
}

function faireUpdateEntreprise(
$fa_entr_cle, $fa_entr_adresse, $fa_entr_tel, $fa_entr_ville, $fa_entr_cp, $fa_entr_opcaRef
) {

    $conn = doConnection("fil_entr");
    $updateString="";
    $recalcul=false;
    if (strlen($fa_entr_adresse) != 0) {
        $updateString.=",adresse='".doDBEscape($fa_entr_adresse)."'";
        $recalcul=true;
    }
    if (strlen($fa_entr_ville) != 0) {
        $updateString.=",ville='".doDBEscape($fa_entr_ville)."'";
    }
    if (strlen($fa_entr_tel) != 0)
        $updateString.=",tel='".doDBEscape($fa_entr_tel)."'";
    if (strlen($fa_entr_cp) != 0)
        $updateString.=",codePostal='".doDBEscape($fa_entr_cp)."'";

    if (strlen($fa_entr_opcaRef) != 0)
        $updateString.=",opcaRef='".doDBEscape($fa_entr_opcaRef)."'";



    $updateString = "update entreprise set ".substr($updateString,1)." where entrepriseCle='".doDBEscape($fa_entr_cle)."';";
    $res = mysql_query($updateString, $conn);

    if ($res == FALSE) {
        return "NOK : Problème avec la requête: $updateString<br/>Raison : " . mysql_error($conn);
    }
    $res = mysql_query("select nom,ville from entreprise where entrepriseCle='".doDBEscape($fa_bur_cle)."'", $conn);
    $result=mysql_fetch_row($res);
    return "OK : ".$result[0]." - ".$result[1];

}

// Ajout du 30/01/2012 JTA Gestion de l'inscription d'un bureau

function faireUpdateBureau($fa_bur_cle,$fa_bur_adresse, $fa_bur_tel, $fa_bur_ville, $fa_bur_cp) {
    $conn = doConnection("fil_entr");
    $updateString="";
    $recalcul=false;
    if (strlen($fa_bur_adresse) != 0) {
        $updateString.=",adresse='".doDBEscape($fa_bur_adresse)."'";
        $recalcul=true;
    }
    if (strlen($fa_bur_ville) != 0) {
        $updateString.=",ville='".doDBEscape($fa_bur_ville)."'";
        $recalcul=true;
    }
    if (strlen($fa_bur_tel) != 0)
        $updateString.=",tel='".doDBEscape($fa_bur_tel)."'";
    if (strlen($fa_bur_cp) != 0)
        $updateString.=",codePostal='".doDBEscape($fa_bur_cp)."'";

    if ($recalcul) {
      $distance = new calculDistance();
      $distanceTmp = $distance->getDistance($fa_bur_adresse." ".$fa_bur_cp." ".$fa_bur_ville);
      $updateString.=",distance='".$distanceTmp."'";
    }

    $updateString = "update bureau set ".substr($updateString,1)." where bureauCle='".doDBEscape($fa_bur_cle)."';";
    $res = mysql_query($updateString, $conn);

    if ($res == FALSE) {
        return "NOK : Problème avec la requête: $updateString<br/>Raison : " . mysql_error($conn);
    }
    $res = mysql_query("select adresse,ville from bureau where bureauCle='".doDBEscape($fa_bur_cle)."'", $conn);
    $result=mysql_fetch_row($res);
    return "OK : SIEGE : ".$result[0]." - ".$result[1];
}

function faireUpdateReferent($fa_ref_cle, $fa_ref_email, $fa_ref_tel,$fa_ref_fonction) {
    $conn = doConnection("fil_entr");
    
    $updateString="";
    if (strlen($fa_ref_email) != 0) {
        $updateString.=",mail='".doDBEscape(($fa_ref_email))."'";
        $recalcul=true;
    }
    if (strlen($fa_ref_tel) != 0) {
        $updateString.=",tel='".doDBEscape(($fa_ref_tel))."'";
        $recalcul=true;
    }
    if (strlen($fa_ref_fonction) != 0)
        $updateString.=",tel='".doDBEscape(($fa_ref_fonction))."'";
    

    $updateString = "update referent set ".substr($updateString,1)." where referentCle='".doDBEscape($fa_ref_cle)."';";
    $res = mysql_query($updateString, $conn);

    if ($res == FALSE) {
        return "NOK : Problème avec la requête: $updateString<br/>Raison : " . mysql_error($conn);
    }
    $res = mysql_query("select concat(prenom,' ',nom,' - ',mail) from referent where referentCle='".doDBEscape($fa_ref_cle)."'", $conn);
    $result=mysql_fetch_row($res);
    return "OK : ".$result[0];
}


// Fin ajout 

//Modification JTA 
function faireUpdateEtudiant(
$groupeRef, $nom, $prenom, $tel, $mail, $et_entrCle, $tuteurRef, $motsCles, $annee, $fa_bureau_cle,$fa_referent_cle
) {

    return;
    $conn = doConnection("fil_dept");

    $etudCle = strtolower($groupeRef) . $nom[0] . $prenom[0] . rand(20, 40);
    $refID = $fa_referent_cle;//Modif ICI
    $altCle = $etudCle . "A";
    $mailLille1 = NULL;

    $res = true;

    $queryString = "set autocommit=0";
    $res = $res && !(!mysql_query($queryString, $conn));
    $queryString = "begin ";
    $res = $res && !(!mysql_query($queryString, $conn));

    //check for ldap id
    $res = getEtudMailAndUIDByNameWithApprox($nom, $prenom);
    //Attention mise en dur car travail en localhost
    //$mailLille1 = "jimmy.tahoulas@univ-lille1.fr";
    //$etudCle = "jimmy.tahoulas2";
    //Fin mise en dur 

    if (count($res) == 1 && $res[0]["details"] === "OK") {
        $etudCle = $res[0]["uid"];
        $mailLille1 = $res[0]["mail"];
    } else {
        $result = "inscrit avec id temporaire. Utilisez la fonctionnalité";
    }


    $res = true;
    $queryString = "Select * from fil_dept.etudiant where  etudCle like '" . $etudCle . "' and anneeReference<=$annee and obsolete=0";
    error_log($queryString);
    $query = mysql_query($queryString, $conn);
    if ($query) {
        $row = mysql_fetch_row($query);
    } else {
        $row = null;
    }
    if ($row) {
        
    } else {
        $queryString = "INSERT INTO " .
                "`etudiant` ( " .
                "`nom`, `prenom`, `tel`, `mail`, `mailLille1`, `etudcle`) VALUES ";

        $queryString = $queryString . "('" . $nom . "',";
        $queryString = $queryString . "'" . $prenom . "',";
        $queryString = $queryString . "'" . $tel . "',";
        $queryString = $queryString . "'" . $mail . "',";
        $queryString = $queryString . "'" . $mailLille1 . "',";
        //TODO ... consider the precise group. Now only one groupe (1) by Formation
        $queryString = $queryString . "'" . $etudCle . "');";
       // echo $queryString . "<br/>";
        mysql_query($queryString, $conn);
        echo mysql_error($conn);
        $res = $res && (mysql_errno($conn) == 0);
    }

    if ($res != false) {
        //groupe
        $queryString = "Select * from fil_dept.etudiant_groupe where  etudRef like '$etudCle' and annee=$annee";
        $query = mysql_query($queryString, $conn);
        if ($query) {
            $row = mysql_fetch_row($query);
        } else {
            $row = null;
        }
        if ($row) {
            
        } else {
            $queryString = "INSERT INTO " .
                    "`etudiant_groupe` ( " .
                    "`annee`, `groupeRef`,`etudRef`) VALUES ";

            $queryString = $queryString . "('" . $annee . "',";
            $queryString = $queryString . "'" . $groupeRef . "',";
            $queryString = $queryString . "'" . $etudCle . "');";
            echo $queryString . "<br/>";

            mysql_query($queryString, $conn);
            echo mysql_error($conn);
            $res = $res && (mysql_errno($conn) == 0);
        }
    }

    if ($res != false) {
        $result.="<br>Update Etudiant ok";
    } else {
        $queryString = "rollback;";
        echo $queryString . "<br/>";
        mysql_query($queryString, $conn);
        echo mysql_error($conn);
        $res = $res && (mysql_errno($conn) == 0);
        return "Echec inscrire étudiant!";
    }

    $connALT = doConnection("stalt");

    $refID = $fa_referent_cle;
    $altCle = $etudCle . "A";

    /*if ($res != false) { inutile
        //$queryString="Insert into fa_referent (`referentCle`, `entrepriseRef`, `ville`) ";
        //$queryString.="values ('".$refID."','".$et_entrCle."','".$ville."');";
        $queryString = "Insert into fa_referent (`referentCle`, `entrepriseRef`) ";
        $queryString.="values ('" . $refID . "','__sans entreprise__');";
        echo $queryString . "<br/>";
        mysql_query($queryString, $connALT);
        echo mysql_error($connALT);
        $res = $res && (mysql_errno($connALT) == 0);
    }*/
// JTA Modif
    if ($res != false) {
        $queryString = "Insert into contrat (`referentRef`, `etudRef`, `tuteurRef`,`alternanceCle`,`opcaRef`,`bureauRef`) ";
        $queryString.="values ('" . $refID . "','" . $etudCle . "','" . $tuteurRef . "','" . $altCle . "','__sans opca__','" . $fa_bureau_cle . "');";
        echo $queryString . "<br/>";
        mysql_query($queryString, $connALT);
        echo mysql_error($connALT);
        $res = $res && (mysql_errno($connALT) == 0);
    }

    if ($res != false) {
        $queryString = "Insert into infoetud (`alternanceRef`,`motscles`) ";
        $queryString.="values ('" . $altCle . "','" . $motsCles . "');";
        echo $queryString . "<br/>";
        mysql_query($queryString, $connALT);
        echo mysql_error($connALT);
        $res = $res && (mysql_errno($connALT) == 0);
    }

    if ($res != false) {
        $queryString = "commit;";
        echo $queryString . "<br/>";
        mysql_query($queryString, $connALT);
        echo mysql_error($connALT);
        $res = $res && (mysql_errno($connALT) == 0);
    }

    if ($res != false) {
        $result.="<br/>Inscription Stage/Alternance ok";
        return $result;
    } else {
        $queryString = "rollback;";
        echo $queryString . "<br/>";
        mysql_query($queryString, $connALT);
        $res = $res && (mysql_errno($connALT) == 0);
        return "Echec inscrire Stage/Alternance";
    }
}

?>
